import openpyxl
import pymysql
import DButils

db = pymysql.connect(
    host='localhost',
    port=3306,
    database='dbase',
    user='root',
    password='root',
    charset='utf8'
)

try:
    with db.cursor() as cursor:
        sql = ("create table user_info("
               "id int(11),"
               "idcard varchar(50),"
               "username varchar(50),"
               "realname varchar(50),"
               "pwd varchar(50),"
               "telphone varchar(12),"
               "email varchar(100),"
               "age int(11),"
               "sex varchar(20),"
               "address varchar(200),"
               "hiredate date,"
               "sal double(9,2),"
               "job varchar(100),"
               "company varchar(100)"
               ")"
               )
        cursor.execute(sql)
        db.commit()
except Exception as e:
    print("数据库操作失败！", e)
finally:
    db.close()
wb = openpyxl.Workbook('student.xlsx')
wt = wb.create_sheet('student')
for i in range(2000):
    wt.append(range(10))
wb.save('student.xlsx')
wb = openpyxl.load_workbook('student.xlsx')
wt = wb['student']
sql = "insert into user_info(id,idcard,username,realname,pwd,telphone,email,age,sex,address) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
for row in wt.rows:
    row_value = []
    for j in row:
        row_value.append(j.value)
    print(tuple(row_value))
    data = tuple(row_value)
    db = DButils.DButils('localhost', 'dbase', 'root', 'root')
    db.update(sql, data)